<?php

    $rootDir = $_SERVER['DOCUMENT_ROOT'];

    require_once($rootDir.'/classes/LeproBJGroup.class.php');
    require_once($rootDir.'/classes/mutex/SQLiteWMutex.class.php');    
    
    // regex match function for sqlite  // http://onwebdevelopment.blogspot.com/2008/08/creating-custom-sqlite-function-in-php.html
    /* sqlite_create_function($db, 'REGEX_MATCH', 'sqlite_regex_match', 2);  
    function sqlite_regex_match($str, $regex) {  
        if (preg_match($regex, $str, $matches) > 0) {  
            return $matches[0];  
        }   
        return false;  
    } */
    
    function sqlite_regex($str, $regex) {
        return (preg_match($regex, $str) > 0);
    }

    class LeproBJGroupDAO {
    
        public function __construct($db) {
            $this->_db = $db;
            //$this->deleteTable(); // TODO: remove
            $this->createTableIfNotExists();
            $this->_db->createFunction('REGEX', 'sqlite_regex', 2);            
        }
    
        public function getAll($packSize=-1, $offset=-1, $orderBy=null, $orderAsc=true, $idsFilterList=null, $userSearchId=null) {
            /* if ($userSearchId != null) {
                $this->_db->createFunction('REGEX', 'sqlite_regex', 2);
            } */
            $res = $this->_db->query('SELECT gid, gcode, users, score, ctime FROM lbjgroups'.
                (($idsFilterList !== null) ? ' WHERE gid IN ('.((count($idsFilterList) > 0) ? implode(',',$idsFilterList) : '').')' : ''). 
                (($userSearchId != null) ? ' WHERE REGEX(users,\'/'.$userSearchId.'($|\s|,)/\') ' : '').
                (($orderBy != null) ? ' ORDER BY '.$orderBy : '').
                (($orderAsc && ($orderBy != null)) ? '' : ' DESC').
                (($packSize > -1) ? ' LIMIT '.$packSize : '').
                (($offset > -1) ? ' OFFSET '.$offset : ''));
            if ($res === false) return null;
            $groupsArr = array();
            foreach ($res->fetchAll() as $grouprow) {
                // $groupsArr[] = new LeproBJGroup($grouprow['gid'], $grouprow['gcode']);\
                $group = new LeproBJGroup($grouprow['gid'], $grouprow['gcode']);
                $group->assignUsers((strlen($grouprow['users']) > 0) ? explode(",",$grouprow['users']) : array());
                $group->setScoreSum($grouprow['score']);
                $group->setCreationTime($grouprow['ctime']);
                $groupsArr[$grouprow['gid']] = $group;
            } 
            return $groupsArr;            
        }
        
        public function save($group) {
            $checkIfExistsQuery = 'SELECT gid FROM lbjgroups WHERE gid = '.$group->getID();
            $res = $this->_db->query($checkIfExistsQuery);
            if ($res === false) return null;            
            if ($res->numRows() == 0) {
                $query = 'INSERT INTO lbjgroups (gid, gcode, score, ctime, ucount, users) VALUES ('.$group->getID().', '.
                                      '\''.$group->getCode().'\', '.
                                      '\''.$group->getScoreSum().'\', '.
                                      '\''.$group->getCreationTime().'\', '.
                                      '\''.$group->getUsersCount().'\', '.
                                      '\''.implode(",",$group->getUsersIDs()).'\''.
                                      ' )';
            } else {
                $query = 'UPDATE lbjgroups SET '.
                                'gcode=\''.$group->getCode().'\', '.
                                'score=\''.$group->getScoreSum().'\', '.
                                'ctime=\''.$group->getCreationTime().'\', '.
                                'ucount=\''.$group->getUsersCount().'\', '.
                                'users=\''.implode(",",$group->getUsersIDs()).'\''.                                      
                                ' WHERE gid = '.$group->getID();
            }
            // $sqliteWMutex = SQLiteWMutex::getInstance();
            SQLiteWMutex::acquireWriteAccess();
            $res = $this->_db->queryExec($query);
            SQLiteWMutex::releaseWriteAccess();
            return $res;
        }        
        
        public function load($groupId) {
            $res = $this->_db->query("SELECT gid, gcode, score, users, ctime FROM lbjgroups WHERE gid = ".$groupId);
            if ($res === false) return null;
            $groupMap = $res->fetchObject();
            if ($groupMap->gid == null) return null;
            $newGroup = new LeproBJGroup($groupMap->gid, $groupMap->gcode);
            $newGroup->setScoreSum($groupMap->score);                        
            $newGroup->assignUsers((strlen($groupMap->users) > 0) ? explode(",",$groupMap->users) : array());
            $newGroup->setCreationTime($groupMap->ctime);
            return $newGroup;         
        }
        
        public function delete($groupId) {
            SQLiteWMutex::acquireWriteAccess();
            $this->_db->queryExec("DELETE FROM lbjgroups WHERE gid = ".$userId);
            SQLiteWMutex::releaseWriteAccess();
        }
        
        public function getCount() {
            return $this->_db->query('SELECT COUNT(*) FROM lbjgroups;')->fetchSingle();
        }
        
        protected function createTableIfNotExists() {
            //$this->_db->queryExec('CREATE TABLE IF NOT EXISTS lusers (id int, lid int, username varchar(50), PRIMARY KEY (lid));');
            $tablesCount = $this->_db->query('SELECT COUNT(*) FROM sqlite_master WHERE name=\'lbjgroups\';')->fetchSingle();
            if ($tablesCount == 0) {
                //$sqliteWMutex = SQLiteWMutex::getInstance();
                SQLiteWMutex::acquireWriteAccess();
                $this->_db->queryExec('CREATE TABLE lbjgroups (gid int, '.                
                                                              'gcode varchar(32), '.
                                                              'score int, '.                                                           
                                                              'users varchar(5120), '.
                                                              'ctime int, '.
                                                              'ucount int, '.
                                                              'PRIMARY KEY (gid));');
                SQLiteWMutex::releaseWriteAccess();
            }
        }
        
        protected function deleteTable() {
            //$sqliteWMutex = SQLiteWMutex::getInstance();
            SQLiteWMutex::acquireWriteAccess();        
            $this->_db->queryExec('DROP TABLE lbjgroups;');
            $this->_db->queryExec('DROP INDEX gscrlgrp;');
            SQLiteWMutex::releaseWriteAccess();
            //if ($res == false) echo 'Failed to delete a table;' else return $res;
        }
        
        /* public function getNextGroupId() {
        
        } */
        
        /* protected function isTableCreated() {
        
        } */
        
        public function deleteAllGroups() {
            $tablesCount = $this->_db->query('SELECT COUNT(*) FROM sqlite_master WHERE name=\'lbjgroups\';')->fetchSingle();
            if ($tablesCount > 0) {        
                $this->deleteTable();
                $this->createTableIfNotExists();
            }
        } 
        
        public function getGroupsScores($groupsIdsList) {        
            if (count($groupsIdsList) > 0) {
                $res = $this->_db->singleQuery('SELECT score FROM lbjgroups WHERE gid IN ('.implode(',',$groupsIdsList).');');
                if ($res === false) return null;
                return (is_string($res) ? array($res) : $res);
            } else return array();
            //if ($userMap->lid == null) return null;
        }   
        
        public function userGroupsCount($userId) {
            return $this->_db->query('SELECT COUNT(*) FROM lbjgroups '.
                                     'WHERE users = '.$userId)->fetchSingle();
        }
        
        public function numOfGroupsWhereUsersMeet($firstUserId, $secondUserId) {
            return $this->_db->query('SELECT COUNT(*) FROM lbjgroups '.
                                     'WHERE REGEX(users,\'/'.$firstUserId.'($|\s|,)/\') '.
                                       'AND REGEX(users,\'/'.$secondUserId.'($|\s|,)/\');')->fetchSingle();         
        }
        
        public function numOfCrossMeets($forUserId, $userIdsList) {
            $sum = 0;
            foreach($userIdsList as $pairedUser) {
                $sum += $this->numOfGroupsWhereUsersMeet($forUserId, $pairedUser);
            }
            return $sum;
        }
        
        public function getGroupPos($groupId) {
            $indexesExists = $this->_db->query('SELECT COUNT(*) FROM sqlite_master WHERE name=\'gscrlgrp\';')->fetchSingle();
            if ($indexesExists == 0) $this->_db->queryExec('CREATE INDEX gscrlgrp on lbjgroups(score);');
            $res = $this->_db->query('SELECT gid FROM lbjgroups ORDER BY score;')->fetchAll(SQLITE_NUM);
            if ($res === false) return null;
            return array_search(array($groupId), $res) + 1;            
        }        
        
        // TODO: public function numberOfCross... $forUserId, $userIdsList 
        // use numOfGroupsWhereUsersMeet for every pair from $forUserId and $userIdsList
    }

?>
